package org.thingsboard.server.dao.sql.tenantip;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.thingsboard.server.dao.model.sql.CamerasInfo;
import org.thingsboard.server.dao.model.sql.TenantIpEntity;
import org.thingsboard.server.dao.model.sql.VideoEntity;


import java.util.List;
import java.util.Map;
import java.util.UUID;

@Repository
public interface TenantIpRepository
        extends PagingAndSortingRepository<TenantIpEntity, UUID>, JpaSpecificationExecutor<TenantIpEntity> {


    //    @Query(value = "SELECT p.ip FROM DeviceEntity d " +
//            "LEFT JOIN UserEntity t on d.tenantId = t.tenantId " +
//            "LEFT JOIN TenantIpEntity p on t.tenantId = p.tenantId " +
//            "WHERE d.name = :deviceName limit 1",nativeQuery = true)
    @Query(value = "SELECT p.ip FROM device d " +
            "LEFT JOIN tb_user t on d.tenant_id = t.tenant_id " +
            "LEFT JOIN tenant_ip p on t.tenant_id = p.tenant_id " +
            "WHERE d.name = :deviceName limit 1", nativeQuery = true)
    String getIpByDeviceName(@Param("deviceName") String deviceName);


    @Query(value = "SELECT p.ip FROM tenant t " +
            "LEFT JOIN tenant_ip p on t.id = p.tenant_id " +
            "WHERE t.title = :tenantName limit 1", nativeQuery = true)
    String getIpByLoginUser(@Param("tenantName") String tenantName);


    @Query(value = "SELECT t.entity_id,t.name,\n" +
            "split_part(split_part(json,',',1),':',2) as cameraId,\n" +
            "split_part(split_part(json,',',2),':',2) as cameraIp from\n" +
            "(SELECT CAST(entity_id AS VARCHAR) entity_id,name,\n" +
            "string_agg(kv.attribute_key||':'||kv.str_v,',')AS json\n" +
            "FROM attribute_kv kv\n" +
            "LEFT JOIN device de on kv.entity_id = de.id\n" +
            "LEFT JOIN tenant te on de.tenant_id = te.id\n" +
            "where te.title = :tenantName and entity_type = 'DEVICE' and \n" +
            "kv.attribute_key in ('cameraId','cameraIp') GROUP BY entity_id,name) as t", nativeQuery = true)
    List<CamerasInfo> getCameras(@Param("tenantName") String tenantName);


    @Query(value = "SELECT DISTINCT ip,str_v cameraid from device\n" +
            "LEFT JOIN tenant_ip tip on device.tenant_id = tip.tenant_id\n" +
            "LEFT JOIN attribute_kv  kv on device.id = kv.entity_id\n" +
            "where name = :device and kv.attribute_key='cameraId'", nativeQuery = true)
    Map getCamerasIdByDeviceName(@Param("device") String device);

//    @Query(value = "SELECT * FROM tenant_ip", nativeQuery = true)
//    List<Map> getIpAndPwd();
}
